﻿using Flare.DB.Entity.Contract;
using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Linq;

namespace Flare.DB
{
    class contract_lists
    {
        public IEnumerable<view_contract_list> Index(string search_string = null, int? id_service = null, int? id_provider = null, int? id_department = null, bool? isclosed = null)
        {
            var result = from c in Source.contract_list()
                         join p in Source.contract_provider() on c.id_provider equals p.id_provider
                         join s in Source.contract_service() on c.id_service equals s.id_service
                         join d in Source.general_department() on c.id_department equals d.id_department

                         where (search_string == null ||
                         c.contract_number.IndexOf(search_string, StringComparison.CurrentCultureIgnoreCase) != -1 ||
                         c.contract_description.IndexOf(search_string, StringComparison.CurrentCultureIgnoreCase) != -1) &&
                         (id_service == null || s.id_service == id_service) &&
                         (id_provider == null || p.id_provider == id_provider) &&
                         (id_department == null || d.id_department == id_department) &&
                         (isclosed == null || isclosed == false || c.isclosed == false)
                         
                         select new view_contract_list
                         {
                             id_contract = c.id_contract,
                             contract_number = c.contract_number,
                             contract_description = c.contract_description,
                             isclosed = c.isclosed,
                             provider = p.provider,
                             service = s.service,
                             department = d.department
                         };
            return result.Take(40);
        }

        public void Create(contract_list data)
        {
            data.id_contract = 0;

            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }

            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(
                        string.Format(@"INSERT INTO [contract_list] ([contract_number],[id_service],[id_provider],[id_department],[contract_description],[isclosed]) 
                                        VALUES('{0}',{1},{2},{3},'{4}',{5})",
                        data.contract_number, data.id_service, data.id_provider, data.id_department, data.contract_description, (data.isclosed) ? 1 : 0), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время добавления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Edit(contract_list data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(
                        string.Format(@"UPDATE [contract_list] SET [contract_number]='{1}',[id_service]={2},[id_provider]={3},[id_department]={4},
                        [contract_description]='{5}', [isclosed]={6} WHERE [id_contract] = {0}",
                        data.id_contract, data.contract_number, data.id_service, data.id_provider, data.id_department, data.contract_description, data.isclosed ? 1 : 0), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время обновления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Delete(int id_contract)
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(
                        string.Format("DELETE FROM [contract_list] WHERE [id_contract] = {0}", id_contract), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время удаления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public contract_list_full Details(int id_contract)
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                var current_list = new contract_list_full();

                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    glob.kernel.Panic(glob.PanicType.db_connect, ex);
                }

                int id_provider = 0;

                using (SqlCeCommand command = new SqlCeCommand(
                        @"SELECT TOP 1 [id_contract],[id_provider],[id_service],[id_department],[contract_number],[provider],[service],[department],[department_address],
                          [department_contact],[department_contact_phone],[contract_description],[isclosed],[id_provider] FROM [view_contract_list] 
                          WHERE id_contract = " + id_contract, connection))
                {
                    try
                    {
                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
                            current_list.id_contract = Convert.ToInt32(reader["id_contract"]);
                            current_list.id_provider = Convert.ToInt32(reader["id_provider"]);
                            current_list.id_service = Convert.ToInt32(reader["id_service"]);
                            current_list.id_department = Convert.ToInt32(reader["id_department"]);
                            current_list.contract_name = string.Format("{0} в {1}", reader["service"], reader["department"], reader["department"]); ;
                            current_list.contract_number = reader["contract_number"].ToString();
                            current_list.contract_region = reader["department_address"].ToString();
                            current_list.contract_bank_contact = reader["department_contact"].ToString();
                            current_list.contract_bank_contact_phone = reader["department_contact_phone"].ToString();
                            current_list.contract_provider = reader["provider"].ToString();
                            current_list.contract_description = (reader["contract_description"].GetType() == typeof(System.DBNull)) ? null : reader["contract_description"].ToString();
                            current_list.isclosed = Convert.ToBoolean(reader["isclosed"]);
                            current_list.contract_provider_phones = new List<contract_providerphone>();
                            id_provider = Convert.ToInt32(reader["id_provider"]);
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText);
                        glob.kernel.Panic(glob.PanicType.sql, ex);
                    }
                }

                using (SqlCeCommand command = new SqlCeCommand(@"SELECT [id_providerphone],[id_provider],[providerphone],[description] 
                                                                FROM [contract_providerphone] WHERE id_provider = @id_provider", connection))
                {
                    command.Parameters.AddWithValue("@id_provider", id_provider);
                    try
                    {
                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var current_phone = new contract_providerphone();
                                current_phone.id_providerphone = Convert.ToInt32(reader["id_providerphone"]);
                                current_phone.id_provider = Convert.ToInt32(reader["id_provider"]);
                                current_phone.providerphone = reader["providerphone"].ToString();
                                current_phone.description = reader["description"].ToString();
                                current_list.contract_provider_phones.Add(current_phone);
                            }

                            if (current_list.contract_provider_phones.Count == 0)
                            {
                                current_list.contract_provider_phones.Add(new contract_providerphone { description = "Отсутствуют" });
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText);
                        glob.kernel.Panic(glob.PanicType.sql, ex);
                    }
                }
                return current_list;
            }
        }

        public int Count()
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"SELECT COUNT(*) FROM [contract_list]", connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    int count = 0;
                    try
                    {
                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
                            count = Convert.ToInt16(reader[0]);
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText);
                        glob.kernel.Panic(glob.PanicType.sql, ex);
                    }
                    return count;
                }
            }
        }
    }
}
